package cn.ml.web3.commons;

import java.io.IOException;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public abstract class DBhelper {
    /**
     * 获取数据库连接
     *   数据库连接参数来自一个配置文件
     *   利用一个工具类 Properties
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws IOException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
        Properties pos = new Properties();
        //加载类路径中的datasource.properties
        pos.load(DBhelper.class.getClassLoader().getResourceAsStream("datasource.properties"));
        Class.forName(pos.getProperty("driver"));
        String url=pos.getProperty("url");
        String user = pos.getProperty("user");
        String pwd = pos.getProperty("pwd");
        Connection conn = DriverManager.getConnection(url,user,pwd);
        return conn;
        //Druid连接池

    }

    /**
     *  用来执行那些非查询的语句
     *  DDL DML
     * @param conn
     * @param sql
     * @param params
     */
    public static void execute(Connection conn,String sql, List<Object> params){
        //PreparedStatement  预处理声明  处理的是带有占位符的sql  ?
        try (PreparedStatement  st = conn.prepareStatement(sql)) {
            if(params !=null){
                for (int i=0;i<params.size();i++){
                    st.setObject(i+1,params.get(i)); //为占位符赋值
                }
            }
            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public interface CallBack{
        void work(ResultSet rs) throws SQLException;
    }

    /**
     * 执行查询语句
     * DQL
     * @param conn
     * @param sql
     * @param params
     * @param cb
     */
    public static void executeQuery(Connection conn,String sql,List<Object> params,CallBack cb){

        try (PreparedStatement st = conn.prepareStatement(sql)) {
            if(params !=null){
                for (int i=0;i<params.size();i++){
                    st.setObject(i+1,params.get(i));
                }
            }
            try(ResultSet rs = st.executeQuery()){
                cb.work(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * JDK 1.7~ try{}finally{}
     * @param conn
     */
    public static void close(Connection conn)  {
        try {
            if(conn!=null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
